﻿Module ProjectDao
    Public Sub insert(ByRef obj As Project)
        DataAccessConnection.openConnection()
        Dim transaction As OracleClient.OracleTransaction
        Dim command As New OracleClient.OracleCommand
        Dim project_id As New Integer
        Dim project_member_id As New Integer
        Dim project_member_role_id As New Integer

        command.Connection = connection

        transaction = connection.BeginTransaction()
        command.Transaction = transaction
        Try
            Dim queryProject As String = "p_insert_project"
            command.CommandType = CommandType.StoredProcedure
            command.CommandText = queryProject
            command.Parameters.AddWithValue("p_name", obj.nameProperty)
            command.Parameters("p_name").Direction = ParameterDirection.Input
            command.Parameters.AddWithValue("p_description", obj.descriptionProperty)
            command.Parameters("p_description").Direction = ParameterDirection.Input
            command.Parameters.AddWithValue("p_start_date", obj.estimatedFromDateProperty)
            command.Parameters("p_start_date").Direction = ParameterDirection.Input
            command.Parameters.AddWithValue("P_end_date", obj.estimatedToDateProperty)
            command.Parameters("P_end_date").Direction = ParameterDirection.Input
            command.Parameters.AddWithValue("p_proyect_responsible", obj.projectResponsibleProperty.idProperty)
            command.Parameters("p_proyect_responsible").Direction = ParameterDirection.Input
            command.Parameters.AddWithValue("P_state_id", obj.stateProperty.idProperty)
            command.Parameters("P_state_id").Direction = ParameterDirection.Input
            command.Parameters.AddWithValue("POUT_ID", project_id)
            command.Parameters("POUT_ID").Direction = ParameterDirection.Output
            command.ExecuteNonQuery()
            project_id = command.Parameters("POUT_ID").Value

            ' Inserta cada uno de los miembros del proyecto
            For Each projectMember As ProjectMember In obj.projectMembersProperty
                Dim queryProjectMembers As String = "p_insert_project_member"
                command.CommandText = queryProjectMembers
                command.Parameters.Clear()
                command.Parameters.AddWithValue("p_available_start_date", projectMember.availableFromDateProperty)
                command.Parameters("p_available_start_date").Direction = ParameterDirection.Input
                command.Parameters.AddWithValue("p_available_end_date", projectMember.availableToDateProperty)
                command.Parameters("p_available_end_date").Direction = ParameterDirection.Input
                command.Parameters.AddWithValue("p_resource_id", projectMember.resourceProperty.idProperty)
                command.Parameters("p_resource_id").Direction = ParameterDirection.Input
                command.Parameters.AddWithValue("p_project_id", project_id)
                command.Parameters("p_project_id").Direction = ParameterDirection.Input
                command.Parameters.AddWithValue("pout_id", project_member_id)
                command.Parameters("pout_id").Direction = ParameterDirection.Output
                command.ExecuteNonQuery()
                project_member_id = command.Parameters("POUT_ID").Value

                ' Inserta cada uno de los roles del miembro del proyecto
                For Each projectMemberRole As ProjectMemberRole In projectMember.projectMemberRoleProperty
                    Dim queryProjectMemberRole As String = "p_insert_project_member_role"
                    command.CommandText = queryProjectMemberRole
                    command.Parameters.Clear()
                    command.Parameters.AddWithValue("p_role_id", projectMemberRole.roleProperty.idProperty)
                    command.Parameters("p_role_id").Direction = ParameterDirection.Input
                    command.Parameters.AddWithValue("p_project_member_id", project_member_id)
                    command.Parameters("p_project_member_id").Direction = ParameterDirection.Input
                    command.Parameters.AddWithValue("pout_id", project_member_role_id)
                    command.Parameters("pout_id").Direction = ParameterDirection.Output
                    command.ExecuteNonQuery()
                Next
            Next

            ' Inserta cada una de las actividades del proyecto
            Dim queryProjectActivities As String = "p_insert_project_activity"
            command.CommandText = queryProjectActivities
            For Each projectActivity As ProjectActivity In obj.projectActivitiesProperty
                Dim id As Integer
                command.Parameters.Clear()
                command.Parameters.AddWithValue("p_estimated_start_date", projectActivity.estimatedFromDateProperty)
                command.Parameters("p_estimated_start_date").Direction = ParameterDirection.Input
                command.Parameters.AddWithValue("p_estimated_end_date", projectActivity.estimatedToDateProperty)
                command.Parameters("p_estimated_end_date").Direction = ParameterDirection.Input
                command.Parameters.AddWithValue("p_project_id", project_id)
                command.Parameters("p_project_id").Direction = ParameterDirection.Input
                command.Parameters.AddWithValue("p_activity_id", projectActivity.activityProperty.idProperty)
                command.Parameters("p_activity_id").Direction = ParameterDirection.Input
                command.Parameters.AddWithValue("pout_id", id)
                command.Parameters("pout_id").Direction = ParameterDirection.Output
                command.ExecuteNonQuery()
            Next

            ' Comitea los cambios
            transaction.Commit()
        Catch ex As Exception
            transaction.Rollback()
            MsgBox("Se produjo un error y no se puedieron guardar los cambios.", MsgBoxStyle.Critical)
        Finally
            DataAccessConnection.closeConnection()
        End Try

    End Sub

    Public Sub update(ByRef project As Project)
        Dim descripcionAccion As String = ""
        DataAccessConnection.openConnection()
        Dim transaction As OracleClient.OracleTransaction
        Dim command As New OracleClient.OracleCommand

        command.Connection = connection
        command.CommandType = CommandType.Text

        transaction = connection.BeginTransaction()
        command.Transaction = transaction
        Try
            descripcionAccion = "Actualizando datos del proyecto. "
            Dim queryProject As String = ""
            queryProject = " UPDATE " & project.getTableName & ""
            queryProject &= "   SET name = '" & project.nameProperty & "' , "
            queryProject &= "       description = '" & project.descriptionProperty & "' , "
            queryProject &= "       start_date = '" & project.estimatedFromDateProperty & "' , "
            queryProject &= "       end_date = '" & project.estimatedToDateProperty & "' , "
            queryProject &= "       proyect_responsible = '" & project.projectResponsibleProperty.idProperty & "' , "
            queryProject &= "       state_id = '" & project.stateProperty.idProperty & "'"
            queryProject &= " WHERE id = " & project.idProperty & ""

            command.CommandText = queryProject
            command.ExecuteNonQuery()


            'Elimino los Roles de los Miembros de Proyecto
            descripcionAccion = "Borrando roles de los miembros del proyecto. "
            For Each projectMember As ProjectMember In project.projectMembersProperty
                Dim queryDeleteProjectMemberRoles As String = ""
                Dim projectMemberRoleObj As New ProjectMemberRole
                queryDeleteProjectMemberRoles = "DELETE FROM " & projectMemberRoleObj.getTableName & " WHERE project_member_id = " & projectMember.idProperty
                command.CommandText = queryDeleteProjectMemberRoles
                command.ExecuteNonQuery()
            Next

            'Elimino los Miembros de Proyecto que actualmente existen en el proyecto
            descripcionAccion = "Borrando miembros del proyecto. "
            Dim queryDeleteProjectMembers As String = ""
            Dim projectMemberObj As New ProjectMember
            queryDeleteProjectMembers = "DELETE FROM " & projectMemberObj.getTableName & " WHERE project_id = " & project.getId
            command.CommandText = queryDeleteProjectMembers
            command.ExecuteNonQuery()

            'Inserto los Miembros de Proyecto actualizados
            descripcionAccion = "Insertando miembros del proyecto. "
            For Each projectMember As ProjectMember In project.projectMembersProperty
                command.CommandType = CommandType.StoredProcedure
                Dim project_member_id As Integer
                Dim queryProjectMembers As String = "p_insert_project_member"
                command.CommandText = queryProjectMembers
                command.Parameters.Clear()
                command.Parameters.AddWithValue("p_available_start_date", projectMember.availableFromDateProperty)
                command.Parameters("p_available_start_date").Direction = ParameterDirection.Input
                command.Parameters.AddWithValue("p_available_end_date", projectMember.availableToDateProperty)
                command.Parameters("p_available_end_date").Direction = ParameterDirection.Input
                command.Parameters.AddWithValue("p_resource_id", projectMember.resourceProperty.idProperty)
                command.Parameters("p_resource_id").Direction = ParameterDirection.Input
                command.Parameters.AddWithValue("p_project_id", projectMember.projectProperty.idProperty)
                command.Parameters("p_project_id").Direction = ParameterDirection.Input
                command.Parameters.AddWithValue("pout_id", project_member_id)
                command.Parameters("pout_id").Direction = ParameterDirection.Output
                command.ExecuteNonQuery()
                project_member_id = command.Parameters("pout_id").Value




                'Dim queryProjectMembersInsert As String = ""
                'queryProjectMembersInsert = "INSERT INTO " & projectMember.getTableName & " (AVAILABLE_START_DATE, AVAILABLE_END_DATE, resource_Id, project_Id) "
                'queryProjectMembersInsert &= "values('" & projectMember.availableFromDateProperty & "', '" & projectMember.availableToDateProperty & "', " & projectMember.resourceProperty.idProperty & ", " & projectMember.projectProperty.idProperty & ")"
                'command.CommandText = queryProjectMembersInsert
                'command.ExecuteNonQuery()

                'Inserto los Roles de los Miembros de Proyecto actualizados
                descripcionAccion = "Insertando roles de los miembros del proyecto. "
                For Each projectMemberRole As ProjectMemberRole In projectMember.projectMemberRoleProperty
                    command.CommandType = CommandType.Text
                    command.Parameters.Clear()
                    Dim queryProjectMemberRoleInsert As String = ""
                    queryProjectMemberRoleInsert = "  INSERT INTO project_member_role "
                    queryProjectMemberRoleInsert &= "	(role_id,"
                    queryProjectMemberRoleInsert &= "	 project_member_id)"
                    queryProjectMemberRoleInsert &= "	VALUES"
                    queryProjectMemberRoleInsert &= "		(:role_id,"
                    queryProjectMemberRoleInsert &= "		 :project_member_id)"
                    command.CommandText = queryProjectMemberRoleInsert
                    command.Parameters.AddWithValue("role_id", projectMemberRole.roleProperty.idProperty)
                    command.Parameters.AddWithValue("project_member_id", project_member_id)

                    'queryProjectMemberRoleInsert = "INSERT INTO " & projectMemberRole.getTableName & " (role_id, project_member_id) "
                    'queryProjectMemberRoleInsert &= "values(" & projectMemberRole.roleProperty.idProperty & ", " & project_member_id & ")"
                    'command.CommandText = queryProjectMemberRoleInsert
                    command.ExecuteNonQuery()
                Next
            Next


            'Elimino las Actividades que actualmente están asociadas al proyecto
            descripcionAccion = "Eliminando las actividades del proyecto. "
            Dim queryDeleteProjectActivities As String = ""
            Dim projectActivityObj As New ProjectActivity
            command.Parameters.Clear()
            queryDeleteProjectActivities = "  DELETE FROM project_activities "
            queryDeleteProjectActivities &= " WHERE project_id = :project_id "
            command.CommandText = queryDeleteProjectActivities
            command.Parameters.AddWithValue("project_id", project.getId)
            command.ExecuteNonQuery()

            'Inserto las Actividades de Proyecto actualizadas
            descripcionAccion = "Insertando las actividades del proyecto. "
            For Each projectActivity As ProjectActivity In project.projectActivitiesProperty
                Dim queryProjectActivitiesInsert As String = ""
                command.Parameters.Clear()
                queryProjectActivitiesInsert = "   INSERT INTO project_activities"
                queryProjectActivitiesInsert &= "   (estimated_start_date,"
                queryProjectActivitiesInsert &= " 	 estimated_end_date,"
                queryProjectActivitiesInsert &= " 	 project_id,"
                queryProjectActivitiesInsert &= " 	 activity_id)"
                queryProjectActivitiesInsert &= " VALUES"
                queryProjectActivitiesInsert &= " 	(:estimated_start_date,"
                queryProjectActivitiesInsert &= " 	 :estimated_end_date,"
                queryProjectActivitiesInsert &= " 	 :project_id,"
                queryProjectActivitiesInsert &= " 	 :activity_id)"
                command.CommandText = queryProjectActivitiesInsert
                command.Parameters.AddWithValue("estimated_start_date", projectActivity.estimatedFromDateProperty)
                command.Parameters.AddWithValue("estimated_end_date", projectActivity.estimatedToDateProperty)
                command.Parameters.AddWithValue("project_id", project.getId)
                command.Parameters.AddWithValue("activity_id", projectActivity.activityProperty.idProperty)
                command.ExecuteNonQuery()
            Next

            transaction.Commit()
        Catch e As Exception
            'MsgBox("Error al insertar el projecto. " & descripcionAccion & e.Message)
            MsgBox("Se produjo un error al guardar los cambios.")
            transaction.Rollback()
        Finally
            DataAccessConnection.closeConnection()
        End Try
    End Sub

    Public Function searchDataByFilter(ByRef project As Project) As Data.DataTable
        DataAccessConnection.openConnection()

        Dim command As New OracleClient.OracleCommand
        Dim table As New Data.DataTable

        Dim state As New State()
        Dim projectResponsible As New Resource()

        Dim query As String = ""
        query = "SELECT pro.id as id, "
        query &= "      pro.name as name, "
        query &= "      pro.description, "
        query &= "      pro.start_date, "
        query &= "      pro.end_date, "
        query &= "      pro.create_date, "
        query &= "      pro.proyect_responsible, "
        query &= "      pro.state_id, "
        query &= "      sta.id state_id, "
        query &= "      sta.name state_name, "
        query &= "      res.id resource_id, "
        query &= "      res.firstname resource_firstname, "
        query &= "      res.surname resource_surname, "
        query &= "      res.document_type resource_document_type, "
        query &= "      res.document_number resource_document_number, "
        query &= "      res.email resource_email, "
        query &= "      res.create_date resource_create_date, "
        query &= "      res.end_date resource_end_date, "
        query &= "      res.hour_cost resource_hour_cost, "
        query &= "      res.state_id  resource_state_id "
        query &= "    from " & project.getTableName & " pro, " & State.getTableName & " sta, " & projectResponsible.getTableName & " res "
        query &= "    where (pro.id = :id OR :id = 0)"
        query &= "  and sta.id = pro.state_id "
        query &= "  and res.id = pro.proyect_responsible "
        query &= "  and UPPER(pro.name) like UPPER('%" & project.nameProperty & "%')"
        query &= "  and pro.description like '%" & project.descriptionProperty & "%'"
        If project.projectResponsibleProperty IsNot Nothing Then
            query &= "  and (pro.proyect_responsible = " & project.projectResponsibleProperty.idProperty & " OR " & project.projectResponsibleProperty.idProperty & " = 0)"
        End If
        If project.stateProperty IsNot Nothing Then
            query &= "  and (pro.state_id = " & project.stateProperty.idProperty & " OR " & project.stateProperty.idProperty & " = 0)"
        End If
        query &= " ORDER BY id"

        command.CommandType = CommandType.Text
        command.CommandText = query
        command.Connection = connection
        command.Parameters.AddWithValue("id", project.idProperty)

        table.Load(command.ExecuteReader)

        DataAccessConnection.closeConnection()

        Return table
    End Function

    Public Function searchDataByFilterSummary(ByRef project As Project) As Data.DataTable
        DataAccessConnection.openConnection()

        Dim command As New OracleClient.OracleCommand
        Dim table As New Data.DataTable

        Dim state As New State()
        Dim projectResponsible As New Resource()

        Dim query As String = ""
        query = "SELECT pro.id as id, "
        query &= "      pro.name as name, "
        query &= "      pro.description, "
        query &= "      pro.start_date, "
        query &= "      pro.end_date, "
        query &= "      sta.name state_name, "
        query &= "      res.firstname resource_firstname, "
        query &= "      res.surname resource_surname "
        query &= "    from " & project.getTableName & " pro, " & state.getTableName & " sta, " & projectResponsible.getTableName & " res "
        query &= "    where (pro.id = :id OR :id = 0)"
        query &= "  and sta.id = pro.state_id "
        query &= "  and res.id = pro.proyect_responsible "
        query &= "  and UPPER(pro.name) like UPPER('%" & project.nameProperty & "%')"
        query &= "  and pro.description like '%" & project.descriptionProperty & "%'"
        If project.projectResponsibleProperty IsNot Nothing Then
            query &= "  and (pro.proyect_responsible = " & project.projectResponsibleProperty.idProperty & " OR " & project.projectResponsibleProperty.idProperty & " = 0)"
        End If
        If project.stateProperty IsNot Nothing Then
            query &= "  and (pro.state_id = " & project.stateProperty.idProperty & " OR " & project.stateProperty.idProperty & " = 0)"
        End If
        query &= " ORDER BY id"

        command.CommandType = CommandType.Text
        command.CommandText = query
        command.Connection = connection
        command.Parameters.AddWithValue("id", project.idProperty)

        table.Load(command.ExecuteReader)

        DataAccessConnection.closeConnection()

        Return table
    End Function

    Public Function searchDataById(ByRef id As Integer) As Project
        Dim table_projects As New Data.DataTable

        Dim project As New Project(id)
        Dim state As New State()
        Dim projectResponsible As New Resource()

        table_projects = searchDataByFilter(project)
        project = convertTableToProject(table_projects)
        Return project
    End Function


    Private Function getMaxProjectMemberId() As Integer
        DataAccessConnection.openConnection()

        Dim command As New OracleClient.OracleCommand
        Dim table As New Data.DataTable
        Dim projectMember As ProjectMember = New ProjectMember
        Dim query As String = ""

        query = "SELECT MAX(id) As id FROM " & projectMember.getTableName & ""

        command.CommandType = CommandType.Text
        command.CommandText = query
        command.Connection = connection

        table.Load(command.ExecuteReader)

        DataAccessConnection.closeConnection()

        Return table.Rows(0)("id")
    End Function

    Private Function convertTableToProject(ByRef table_projects As Data.DataTable) As Project
        Dim project As New Project
        Dim state As New State()
        Dim projectResponsible As New Resource()

        Dim list_project_members As New ArrayList
        Dim list_project_activities As New ArrayList

        project.idProperty = table_projects.Rows(0)("id")
        project.nameProperty = table_projects.Rows(0)("name")
        project.descriptionProperty = table_projects.Rows(0)("description")
        project.estimatedFromDateProperty = table_projects.Rows(0)("start_date")
        project.estimatedToDateProperty = table_projects.Rows(0)("end_date")
        project.createDateProperty = table_projects.Rows(0)("create_date")

        projectResponsible.idProperty = table_projects.Rows(0)("resource_id")
        projectResponsible.firstnameProperty = table_projects.Rows(0)("resource_firstname")
        projectResponsible.surnameProperty = table_projects.Rows(0)("resource_surname")
        projectResponsible.documentTypeProperty = table_projects.Rows(0)("resource_document_type")
        projectResponsible.documentNumberProperty = table_projects.Rows(0)("resource_document_number")
        projectResponsible.emailProperty = table_projects.Rows(0)("resource_email")
        projectResponsible.createDateProperty = table_projects.Rows(0)("resource_create_date")
        projectResponsible.endDateProperty = IIf(IsDBNull(table_projects.Rows(0)("resource_end_date")), Nothing, table_projects.Rows(0)("resource_end_date"))
        projectResponsible.hourCostProperty = IIf(IsDBNull(table_projects.Rows(0)("resource_hour_cost")), Nothing, table_projects.Rows(0)("resource_hour_cost"))
        project.projectResponsibleProperty = projectResponsible

        state.idProperty = table_projects.Rows(0)("state_id")
        state.nameProperty = table_projects.Rows(0)("state_name")
        project.stateProperty = state

        Dim projectMember As New ProjectMember(project)
        list_project_members = ProjectMemberDao.searchData(projectMember)
        project.projectMembersProperty = list_project_members

        Dim projectActivity As New ProjectActivity(project)
        list_project_activities = ProjectActivityDao.searchData(projectActivity)
        project.projectActivitiesProperty = list_project_activities

        Return project
    End Function

    Public Sub updateProjectState(ByRef project As Project)
        DataAccessConnection.openConnection()
        Dim command As New OracleClient.OracleCommand
        command.Connection = connection
        Dim query As String = ""
        query = "   UPDATE projects "
        query &= "	   SET state_id = :state_id "
        query &= "   WHERE id = :id "
        command.CommandType = CommandType.Text
        command.CommandText = query
        command.Parameters.AddWithValue("state_id", project.stateProperty.idProperty)
        command.Parameters.AddWithValue("id", project.idProperty)
        command.ExecuteNonQuery()
        DataAccessConnection.closeConnection()
    End Sub
End Module
